package org.management.utils;



import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 加载模板，使用单例模式
 * @author jiangyan
 *
 */
public class ExcelTamplate {
	/**
	 * 定义开始填充数据的表示,表示从模板中datas标示的单元格开始填充数据
	 */
	public final static String DATA_LINE = "datas"; 
	
	/**
	 * 默认样式，在模板中标有defaultStyles的就会读取这个样式
	 */
	public final static String DEFAULT_STYLE="defaultStyles";
	
	/**
	 * 样式，模板里标有styles的，说明使用自己的样式
	 */
	public final static String STYLE="styles";
	
	/**
	 * 序号
	 */
	public final static String SER_NUM ="sernums";
	
	/**
	 * 开始初始化列的下标
	 */
	private int initCollIndex;
	
	/**
	 * 开始初始化行的下标
	 */
	private int initRowIndex;
	
	/**
	 * 当前列的下标
	 */
	private int curCellIndex;
	
	/**
	 * 当期行的下标
	 */
	private int curRowIndex;
	
	private Sheet sheet ;
	
	/**
	 * 当前行
	 */
	private Row curRow;
	
	/**
	 * 页脚数据的行下标 
	 */
	private int lastRowIndex;
	
	/**
	 * 默认样式
	 */
	private CellStyle defaultStyle;
	
	/**
	 * 行高
	 */
	private float rowHeight;
	
	/**
	 * 存储每一列对应的样式
	 */
	private Map<Integer, CellStyle> styles;
	
	/**
	 * 序号的列号
	 */
	private int serColIndex;
	
	private static ExcelTamplate eTamplate = new ExcelTamplate();
	private Workbook wb;
	private ExcelTamplate(){
		
	}
	public Workbook getWb() {
		return wb;
	}
	public static ExcelTamplate getInstance() {
		return eTamplate;
	}
	
	/**
	 * 1、读取相应的模板文档,基于类路径和基于文本路径两种方式；
	 * @param path
	 * @return
	 */
	public ExcelTamplate readTemplateByClasspath(String path) {
		try {
			wb = WorkbookFactory.create(ExcelTamplate.class.getResourceAsStream(path));
			//加载完成，初始化
			initTemplate();
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException("读入的模板格式有错或不存在，请检查");
		}
		return eTamplate;
	}
	public ExcelTamplate readTemplateByPath(String path)  {
		try {
			wb = WorkbookFactory.create(new File(path));
			//加载完成，初始化
			initTemplate();
		}catch(InvalidFormatException eInvalidFormatException) {
			eInvalidFormatException.printStackTrace();
		}catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("读入的模板格式有错或不存在，请检查");
		}
		return eTamplate;
	}
	
	/**
	 * 写书xls文件
	 * @param filePath
	 */
	public void writeToFile(String filePath)  {
		FileOutputStream fos = null;
		//使得页脚往下移动
		sheet.shiftRows(0, 7, 1, true, true);
		try {
			fos = new FileOutputStream(filePath);
			wb.write(fos);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
			throw new RuntimeException("写入的文件存在!");
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("写入数据失败!");
		}finally{
			if (null != fos) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	public void writeToStream(OutputStream out) {
		try {
			wb.write(out);
		} catch (IOException e) {
			e.printStackTrace();
			throw new RuntimeException("写入的流不存在!");
		}
	}
	
	/**
	 * 初始化模板
	 */
	private void initTemplate() {
		//得到sheet表格
		sheet = wb.getSheetAt(0);
		initConfigData();
		lastRowIndex = sheet.getLastRowNum();
		curRow = sheet.createRow(curRowIndex);
	}
	/**
	 * 初始化配置数据
	 */
	public void initConfigData() {
		boolean findData = false;
		boolean findSer = false;
		//遍历所有行和列，查到要填充数据的开始位置
		for (Row row : sheet) {
			if (findData) { 
				break; 
			};
			for (Cell c : row) {
				//默认情况下设置标示开始位置的类型为String 所以不是Stirng 的直接跳过
				String str = c.getStringCellValue().trim();
				//找到有要加入序号的标示
				if (SER_NUM.equals(str)) {
					serColIndex = c.getColumnIndex();
					findSer = true;
				}
				if (DATA_LINE.equals(str)) {
					//记录初始化的行和列
					initCollIndex = c.getColumnIndex();
					initRowIndex = row.getRowNum();
					//当前的行等于初始化的行,列等于列
					curCellIndex = initCollIndex;
					curRowIndex = initRowIndex;
					defaultStyle = c.getCellStyle();
					rowHeight = row.getHeightInPoints();
					initStyles();
					findData = true;
					break;
				}
			}
			if (!findSer) {
				initSer();
			}
		}
		
		lastRowIndex = curRowIndex;
		curRow = sheet.createRow(curRowIndex);
	}
	
	private void initSer() {
		for (Row row:sheet) {
			for (Cell c:row) {
				String str = c.getStringCellValue().trim();
				//找到有要加入序号的标示
				if (SER_NUM.equals(str)) {
					serColIndex = c.getColumnIndex();
				}
			}
		}
	}
	
	private void initStyles() {
		styles = new HashMap<Integer, CellStyle>(sheet.getLastRowNum());
		for(Row row:sheet) {
			for(Cell c:row) {
				String str = c.getStringCellValue().trim();
				if(DEFAULT_STYLE.equals(str)) {
					defaultStyle = c.getCellStyle();
				}
				if(STYLE.equals(str)) {
					styles.put(c.getColumnIndex(), c.getCellStyle());
				}
			}
		}
	}
	/**
	 * 填充数据
	 * @param value
	 * @return
	 */
	public Cell createCell(String value) {
		Cell cell = curRow.createCell(curCellIndex);
		cell.setCellValue(value);
		setCellStyle(cell);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	
	public void margeCurCell(Integer firstIndex, Integer lastIndex) {
		CellRangeAddress cellAdress = new CellRangeAddress(curRowIndex-1,curRowIndex-1,firstIndex,lastIndex);
		sheet.addMergedRegion(cellAdress);
	}
	
	public void margeRowCell(Integer rowIndex, Integer lastRowIndex, Integer cellIndex) {
		sheet.addMergedRegion(new CellRangeAddress(rowIndex,lastRowIndex,cellIndex,cellIndex));
	}
	
	public int getCurRowIndex(){
		return curRowIndex;
	}
	public Cell createCell(String value,String format){
		
		Cell cell = curRow.createCell(curCellIndex);
		cell.setCellValue(Double.parseDouble(value));
		CellStyle style = wb.createCellStyle();
		style.setDataFormat(wb.createDataFormat().getFormat(format));
		cell.setCellStyle(style);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	/**
	 * 填充数据
	 * @param value
	 * @return
	 */
	public Cell createCell(int value) {
		Cell cell = curRow.createCell(curCellIndex);
		setCellStyle(cell);
		cell.setCellValue((int)value);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	
	/**
	 * 填充数据
	 * @param value
	 * @return
	 */
	public Cell createCell(double value) {
		Cell cell = curRow.createCell(curCellIndex);
		setCellStyle(cell);
		cell.setCellValue(value);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	/**
	 * 填充数据
	 * @param value
	 * @return
	 */
	public Cell createCell(boolean value) {
		Cell cell = curRow.createCell(curCellIndex);
		setCellStyle(cell);
		cell.setCellValue(value);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	/**
	 * 填充数据
	 * @param value
	 * @return
	 */
	public Cell createCell(Calendar value) {
		Cell cell = curRow.createCell(curCellIndex);
		setCellStyle(cell);
		cell.setCellValue(value);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	/**
	 * 填充数据
	 * @param value
	 * @return
	 */
	public Cell createCell(Date value) {
		Cell cell = curRow.createCell(curCellIndex);
		setCellStyle(cell);
		cell.setCellValue(value);
		//填充完成，当前列加加
		curCellIndex++;
		return cell;
	}
	/**
	 * 填完一行就要创建新的行
	 */
	public void createNewRow() {
		//如果有最后一行，则下移动
		if (lastRowIndex > curRowIndex && curRowIndex != initRowIndex) {
			sheet.shiftRows(curRowIndex, lastRowIndex, 1, true, true);
			lastRowIndex ++;
		}
		curRow = sheet.createRow(curRowIndex);
		curRow.setHeightInPoints(rowHeight);
		curRowIndex ++;
		curCellIndex = initCollIndex;
	}
	/**
	 * 根据传进来的map替换相应的常量
	 */
	public void replaceFinalData(Map<String,String> datas) {
		for (Row row:sheet) {
			for (Cell c:row) {
				String str = c.getStringCellValue().trim();
				if (str.indexOf("${") > -1) {
					for (Entry<String, String> entry : datas.entrySet()) {
						String key = "${"+entry.getKey()+"}";
						String value = entry.getValue();
						if (str.indexOf(key) > -1) {
							if (value instanceof CharSequence) {
								str.replace("${"+entry.getKey()+"}", value);
							}	
						}
					}
					c.setCellValue(str);
				}
			}
		}
	}
	
	
	/**
	 * 根据传进来的map替换相应的常量 待格式
	 */
	public void replaceFinalData(Map<String,String> datas,String format) {
		for (Row row:sheet) {
			for (Cell c:row) {
				String str = c.getStringCellValue().trim();
				if (str.indexOf("${") > -1) {
					for (Entry<String, String> entry : datas.entrySet()) {
						String key = "${"+entry.getKey()+"}";
						String value = entry.getValue();
						if (str.indexOf(key) > -1) {
							if (value instanceof CharSequence) {
								str.replace("${"+entry.getKey()+"}", value);
							}	
						}
					}
					CellStyle style = wb.createCellStyle();
					style.setDataFormat(wb.createDataFormat().getFormat(format));
					c.setCellStyle(style);
					c.setCellValue(Double.parseDouble(str));
				}
			}
		}
	}
	/**
	 * 插入序号
	 */
	public void insertSer() {
		int index = 1;
		Row row = null;
		Cell cell = null;
		for(int i = initRowIndex; i<curRowIndex; i++) {
			row = sheet.getRow(i);
			cell = row.createCell(serColIndex);
			setCellStyle(cell);
			cell.setCellValue(index++);
		}
	}
	private void setCellStyle(Cell cell) {
		if(styles.containsKey(curCellIndex)) {
			cell.setCellStyle(styles.get(curCellIndex));
		}else {
			cell.setCellStyle(defaultStyle);
		}
	}
	/**
	 * 设置下表名称
	 * @param names
	 */
	public void setSheet(String[] names) {
		for(int i=0; i< names.length; i++) {
			this.wb.setSheetName(i, names[i]);
		}
	}
	public Sheet getSheet() {
		return sheet;
	}
	public void setSheet(Sheet sheet) {
		this.sheet = sheet;
	}
	
	public void initConfigData(Sheet sheet) {
		boolean findData = false;
		boolean findSer = false;
		//遍历所有行和列，查到要填充数据的开始位置
		this.sheet = sheet;
		for (Row row : sheet) {
			if (findData) {
				break;
			}
			for (Cell c : row) {
				//默认情况下设置标示开始位置的类型为String 所以不是Stirng 的直接跳过
				String str = c.getStringCellValue().trim();
				//找到有要加入序号的标示
				if(SER_NUM.equals(str)) {
					serColIndex = c.getColumnIndex();
					findSer = true;
				}
				if(DATA_LINE.equals(str)) {
					//记录初始化的行和列
					initCollIndex = c.getColumnIndex();
					initRowIndex = row.getRowNum();
					//当前的行等于初始化的行,列等于列
					curCellIndex = initCollIndex;
					curRowIndex = initRowIndex;
					defaultStyle = c.getCellStyle();
					rowHeight = row.getHeightInPoints();
					initStyles();
					findData = true;
					break;
				}
			}
			if (!findSer) {
				initSer();
			}
		}
		lastRowIndex = sheet.getLastRowNum();
		curRow = sheet.createRow(curRowIndex);
	}
	
	/**
	 * 合并行
	 * @param datas
	 */
	public void replaceFinalData(int cellIndex) {
		// 参数设置
		int startIndex = -1;
		int endIndex = -1;
		String cellValue = "";
		
		// 循环列
		for(int i = 0; i <= sheet.getLastRowNum(); i++) {
			String value = sheet.getRow(i).getCell(cellIndex)==null?"":sheet.getRow(i).getCell(cellIndex).getStringCellValue();
			if(cellValue.equals(value)) {
				endIndex++;
				if(i == sheet.getLastRowNum()) {
					sheet.addMergedRegion(new CellRangeAddress(startIndex,endIndex,cellIndex,cellIndex));
				}
			} else {
				// 合并
				if(startIndex > -1 && endIndex > -1 && startIndex != endIndex) {
					sheet.addMergedRegion(new CellRangeAddress(startIndex,endIndex,cellIndex,cellIndex));
				}
				// 重置参数
				cellValue = value;
				startIndex = ++endIndex;
			}
		}
		
	}
	
	/** 
     * Sheet复制 
     * @param fromSheet 
     * @param toSheet 
     * @param copyValueFlag 
     */  
 public void copySheet(Sheet fromSheet, boolean copyValueFlag) {
	 Row startRow = fromSheet.getRow(0);
	 for (Row row : fromSheet) {
		 mergerRegion(fromSheet,this.sheet);
		 Row newrow = this.sheet.createRow(row.getRowNum());
		 newrow.setHeight(row.getHeight());
		 copyRow(row,newrow,copyValueFlag);
	}
	 for (Cell cell : startRow) {
		 short width = (short)fromSheet.getColumnWidth(cell.getColumnIndex());
		 this.sheet.setColumnWidth((short) cell.getColumnIndex(), (short) width);	}
	 
 }
 /** 
  * 行复制功能 
  * @param fromRow 
  * @param toRow 
  */  
 public void copyRow(Row fromRow,Row newrow,boolean copyValueFlag){ 
	 for (Cell cell : fromRow) {
		 Cell newCell = newrow.createCell(cell.getColumnIndex());
		 copyCell(cell, newCell, copyValueFlag);  
	}  
 } 
 
 /** 
  * 复制原有sheet的合并单元格到新创建的sheet 
  *  
  * @param sheetCreat 新创建sheet 
  * @param sheet      原有的sheet 
  */  
  public static void mergerRegion(Sheet fromSheet, Sheet toSheet) {  
     int sheetMergerCount = fromSheet.getNumMergedRegions();  
     for (int i = 0; i < sheetMergerCount; i++) {  
      toSheet.addMergedRegion(fromSheet.getMergedRegion(i));  
     }  
  } 
 
 /** 
  * 复制单元格 
  *  
  * @param srcCell 
  * @param distCell 
  * @param copyValueFlag 
  *            true则连同cell的内容一起复制 
  */  
 public void copyCell(Cell srcCell, Cell distCell, boolean copyValueFlag) {  
	 CellStyle newstyle = srcCell.getCellStyle();
	 distCell.setCellStyle(newstyle);
     distCell.setCellValue(srcCell.getStringCellValue());
    
 } 
 
}
